"""Promotes values for better query performance.

Revision ID: ce5c4ac967d8
Revises: 3097592c0739
Create Date: 2021-11-18 10:09:22.330772

"""
from alembic import op
from collections import Counter
import enum

from sqlalchemy import Column, DateTime, ForeignKey, Integer, String, Numeric
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, Session


Base = declarative_base()


# revision identifiers, used by Alembic.
revision = "ce5c4ac967d8"
down_revision = "3097592c0739"
branch_labels = None
depends_on = None


class ParticipantRoleType(enum.Enum):
    incident_commander = "Incident Commander"
    scribe = "Scribe"
    liaison = "Liaison"
    participant = "Participant"
    reporter = "Reporter"


class DocumentResourceTypes(enum.Enum):
    executive = "dispatch-executive-report-document"
    review = "dispatch-incident-review-document"
    tracking = "dispatch-incident-sheet"
    incident = "dispatch-incident-document"


class IncidentCost(Base):
    __tablename__ = "incident_cost"
    id = Column(Integer, primary_key=True)
    amount = Column(Numeric(precision=10, scale=2), nullable=True)
    incident_id = Column(Integer, ForeignKey("incident.id"))


class ParticipantRole(Base):
    __tablename__ = "participant_role"
    id = Column(Integer, primary_key=True)
    assumed_at = Column(DateTime)
    renounced_at = Column(DateTime)
    role = Column(String, default=ParticipantRoleType.participant)
    participant_id = Column(Integer, ForeignKey("participant.id"))


class Participant(Base):
    __tablename__ = "participant"
    id = Column(Integer, primary_key=True)
    team = Column(String)
    location = Column(String)
    participant_roles = relationship("ParticipantRole", backref="participant")
    incident_id = Column(Integer, ForeignKey("incident.id"))


class Document(Base):
    __tablename__ = "document"
    id = Column(Integer, primary_key=True)
    incident_id = Column(Integer, ForeignKey("incident.id"))
    resource_type = Column(String)


class Group(Base):
    __tablename__ = "group"
    id = Column(Integer, primary_key=True)
    incident_id = Column(Integer, ForeignKey("incident.id"))
    resource_type = Column(String)


class Incident(Base):
    __tablename__ = "incident"
    id = Column(Integer, primary_key=True)
    total_cost = Column(Numeric)
    participants_team = Column(String)
    participants_location = Column(String)
    commanders_location = Column(String)
    reporters_location = Column(String)

    incident_costs = relationship("IncidentCost")
    documents = relationship("Document", foreign_keys=[Document.incident_id])
    participants = relationship("Participant", foreign_keys=[Participant.incident_id])
    groups = relationship("Group", foreign_keys=[Group.incident_id])

    commander_id = Column(Integer, ForeignKey("participant.id"))
    reporter_id = Column(Integer, ForeignKey("participant.id"))
    liaison_id = Column(Integer, ForeignKey("participant.id"))
    scribe_id = Column(Integer, ForeignKey("participant.id"))
    incident_document_id = Column(Integer, ForeignKey("document.id"))
    incident_review_document_id = Column(Integer, ForeignKey("document.id"))
    tactical_group_id = Column(Integer, ForeignKey("group.id"))
    notifications_group_id = Column(Integer, ForeignKey("group.id"))


def get_current_participant(participants, role):
    participant_roles = []
    for p in participants:
        for pr in p.participant_roles:
            if pr.role == role.value:
                participant_roles.append(pr)

    if participant_roles:
        return sorted(participant_roles, key=lambda pr: pr.assumed_at)[-1].participant


def get_current_document(documents, resource_type):
    for d in documents:
        if d.resource_type == resource_type:
            return d


def get_current_group(groups, resource_type):
    for g in groups:
        if g.resource_type:
            if g.resource_type.endswith(resource_type):
                return g


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column("incident", Column("total_cost", Numeric(), nullable=True))
    op.add_column("incident", Column("participants_team", String(), nullable=True))
    op.add_column("incident", Column("participants_location", String(), nullable=True))
    op.add_column("incident", Column("commanders_location", String(), nullable=True))
    op.add_column("incident", Column("reporters_location", String(), nullable=True))
    op.add_column("incident", Column("commander_id", Integer(), nullable=True))
    op.add_column("incident", Column("reporter_id", Integer(), nullable=True))
    op.add_column("incident", Column("liaison_id", Integer(), nullable=True))
    op.add_column("incident", Column("scribe_id", Integer(), nullable=True))
    op.add_column("incident", Column("incident_document_id", Integer(), nullable=True))
    op.add_column("incident", Column("incident_review_document_id", Integer(), nullable=True))
    op.add_column("incident", Column("tactical_group_id", Integer(), nullable=True))
    op.add_column("incident", Column("notifications_group_id", Integer(), nullable=True))
    op.create_foreign_key(None, "incident", "document", ["incident_document_id"], ["id"])
    op.create_foreign_key(None, "incident", "participant", ["reporter_id"], ["id"])
    op.create_foreign_key(None, "incident", "participant", ["scribe_id"], ["id"])
    op.create_foreign_key(None, "incident", "participant", ["commander_id"], ["id"])
    op.create_foreign_key(None, "incident", "participant", ["liaison_id"], ["id"])
    op.create_foreign_key(None, "incident", "document", ["incident_review_document_id"], ["id"])
    op.create_foreign_key(None, "incident", "group", ["tactical_group_id"], ["id"])
    op.create_foreign_key(None, "incident", "group", ["notifications_group_id"], ["id"])

    print("Starting data migration...")

    bind = op.get_bind()
    session = Session(bind=bind)

    incidents = session.query(Incident).all()

    for incident in incidents:
        # we set the total cost
        cost = 0
        for c in incident.incident_costs:
            cost += c.amount
        incident.total_cost = cost

        # we set the participants team, and participants, commanders, and reporters location
        incident.participants_team = Counter(p.team for p in incident.participants).most_common(1)[
            0
        ][0]
        incident.participants_location = Counter(
            p.location for p in incident.participants
        ).most_common(1)[0][0]

        commander = get_current_participant(
            incident.participants, ParticipantRoleType.incident_commander
        )
        if commander:
            incident.commander_id = commander.id
            incident.commanders_location = commander.location

        reporter = get_current_participant(incident.participants, ParticipantRoleType.reporter)
        if reporter:
            incident.reporter_id = reporter.id
            incident.reporters_location = reporter.location

        liaison = get_current_participant(incident.participants, ParticipantRoleType.liaison)
        if liaison:
            incident.liaison_id = liaison.id

        scribe = get_current_participant(incident.participants, ParticipantRoleType.scribe)
        if scribe:
            incident.scribe_id = scribe.id

        # we set the incident document and post-incident review document foreign keys
        incident_document = get_current_document(incident.documents, DocumentResourceTypes.incident)
        if incident_document:
            incident.incident_document_id = incident_document.id

        incident_review_document = get_current_document(
            incident.documents, DocumentResourceTypes.review
        )
        if incident_review_document:
            incident.incident_review_document_id = incident_review_document.id

        # we set the tactical and notifications foreign keys
        tactical_group = get_current_group(incident.groups, "tactical-group")
        if tactical_group:
            incident.tactical_group_id = tactical_group.id

        notifications_group = get_current_group(incident.groups, "notifications-group")
        if notifications_group:
            incident.notifications_group_id = notifications_group.id

    session.commit()

    print("Data migration completed.")
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(None, "incident", type_="foreignkey")
    op.drop_constraint(None, "incident", type_="foreignkey")
    op.drop_constraint(None, "incident", type_="foreignkey")
    op.drop_constraint(None, "incident", type_="foreignkey")
    op.drop_constraint(None, "incident", type_="foreignkey")
    op.drop_constraint(None, "incident", type_="foreignkey")
    op.drop_column("incident", "tactical_group_id")
    op.drop_column("incident", "notifications_group_id")
    op.drop_column("incident", "incident_review_document_id")
    op.drop_column("incident", "incident_document_id")
    op.drop_column("incident", "scribe_id")
    op.drop_column("incident", "liaison_id")
    op.drop_column("incident", "reporter_id")
    op.drop_column("incident", "commander_id")
    op.drop_column("incident", "primary_location")
    op.drop_column("incident", "primary_team")
    op.drop_column("incident", "total_cost")
    # ### end Alembic commands ###
